package com.cxj.musicplayer.utils;


import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.sql.Blob;
import java.util.ArrayList;
/*
工具类 连接操作sqlite

ex:
    第一步，获取一个数据库工具类对象，参数为:<当前activity或context>, <数据库名称>, <数据库表名称>, <表对应的JavaBean class>
    SqliteUtil sqliteUtil = new SqliteUtil(this, "py.db", "py", Py.class);
    顺带一提:
        JavaBean必须所有属性都是public, 而且必须有Integer类型的属性 “_id” 作为主键
        所有的类型都必须是包装类类型:如 int 写成 Integer float 写成 Float
        下面为示例代码,要有无参构造方法
        """
        package com.example.wechat.Bean;

        public class Py {
            public Integer _id;
            public String _head_photo;
            public String _name;
            public String _time;
            public String _msg;

            public Py(){

            }
            public Py(Integer _id, String _head_photo, String _name, String _time, String _msg){
                this._id = _id;
                this._head_photo = _head_photo;
                this._name = _name;
                this._time = _time;
                this._msg = _msg;
            }
        }


        """
    第二步:
        插入使用:
            sqliteUtil.insert(new Py(null, "ywx_5_3", "伟贤", "2020/3/12", "勾线"));
            参数为一个表对应的JavaBean对象
    第三步:
        更新使用:
            sqliteUtil.update(py);
            py为更新的对象
    第四步:
        删除使用:
            sqliteUtil.deleteById(id);
            传入id删除,未完美测试
    第五步:
         查询使用:
            sqliteUtil.query(null, null, null, null, null, null);
            参数全为空则为全查
            第一个参数：字符串数组 new String[]{"_id"}代表只查_id这一列
            其他暂时用不到，需要自行了解
     第六步:
            用完调用:close_()方法关闭；
 */
public class SqliteUtil extends SQLiteOpenHelper {
    private String dbName;
    private String tableName;
    private Class clazz;
    private String sql;
    private final int NULL  = 0;
    private final int INTEGER = 1;
    private final int FLOAT = 2;
    private final int STRING = 3;
    private final int BLOB = 4;
    private final int NOTHING = -1;

    public SqliteUtil(Context context, String dbName, String tableName, Class clazz){
        super(context, dbName, null, 1);
        this.dbName = dbName;
        this.tableName = tableName;
        this.clazz = clazz;
        try {
            this.sql = createSql();
            System.out.println("自动生成:" + sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private int getType(Class c){
        if(c == String.class){
            return 3;
        }else if(c == Integer.class){
            return 1;
        }else  if(c == Float.class){
            return 2;
        }else if(c == null){
            return 0;
        }else if(c == Blob.class){
            return 4;
        }
        return -1;
    }
    private String createSql() throws Exception {
        StringBuilder sql = new StringBuilder("create table " + tableName + " (");
        for(Field f: clazz.getFields()){
            sql.append(f.getName()).append(" ");
            switch (getType(f.getType())){
                case NOTHING:
                case NULL:
                    throw new Exception("表创建类型出错");
                case INTEGER:
                    sql.append("INTEGER ");
                    break;
                case FLOAT:
                    sql.append("REAL ");
                    break;
                case STRING:
                    sql.append("TEXT ");
                    break;
                case BLOB:
                    sql.append("BLOB ");
                    break;
            }
            if(f.getName().equals("_id")){
                sql.append("primary key autoincrement ");
            }
            sql.append(" , ");
        }
        int index = sql.lastIndexOf(",");
        sql.replace(index, index + 1, ");");
        return sql.toString();
    }

    public ArrayList<Object> query(String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy ) throws InstantiationException, IllegalAccessException, NoSuchFieldException {
        SQLiteDatabase db = getReadableDatabase();

        Cursor cursor = db.query(tableName, columns, selection, selectionArgs, groupBy, having, orderBy);
        ArrayList<Object> res = new ArrayList<>();
        if(cursor.moveToFirst()){
            String[] s = cursor.getColumnNames();

            do{
                Object o = clazz.newInstance();
                for(String n: s){
                    int index = cursor.getColumnIndex(n);
                    int type = cursor.getType(index);
                    switch(type){
                        case 0:
                            clazz.getField(n).set(o, null);
                            break;
                        case 1:
                            clazz.getField(n).set(o, cursor.getInt(index));
                            break;
                        case 2:
                            clazz.getField(n).set(o, cursor.getFloat(index));
                            break;
                        case 3:
                            clazz.getField(n).set(o, cursor.getString(index));
                            break;
                        case 4:
                            clazz.getField(n).set(o, cursor.getBlob(index));
                            break;
                    }
                }
                res.add(o);
            }while (cursor.moveToNext());
        }
        cursor.close();
        return res;
    }

    public void insert(Object o) throws IllegalAccessException {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        Field[] fields = clazz.getFields();
        for (Field f: fields){
            if(null != f.get(o)) {
                if (f.getType() == String.class)
                    contentValues.put(f.getName(), String.valueOf(f.get(o)));
                if (f.getType() == Integer.class)
                    contentValues.put(f.getName(), (Integer) (f.get(o)));
                if (f.getType() == Float.class)
                    contentValues.put(f.getName(), (Float) (f.get(o)));
            }
        }
        db.insert(tableName, null, contentValues);
    }

    public void update(Object o) throws IllegalAccessException, NoSuchFieldException {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        for(Field f: clazz.getFields()){
            if (f.getType() == String.class)
                values.put(f.getName(), String.valueOf(f.get(o)));
            if (f.getType() == Integer.class)
                values.put(f.getName(), (Integer)(f.get(o)));
            if (f.getType() == Float.class)
                values.put(f.getName(), (Double) (f.get(o)));
        }
        long ret = -1;
        do {
            Field primary_key = clazz.getField("_id");
            ret = db.update(tableName, values, primary_key.getName() + "=?",
                    new String[] { String.valueOf(primary_key.get(o)) });
        } while (ret < 0);
    }

    public int deleteById(int userId) throws NoSuchFieldException {
        SQLiteDatabase db = getWritableDatabase();
        if (userId == 0) {
            return -1;
        }
        Field primary_key = clazz.getField("_id");
        return db.delete(tableName, primary_key.getName() + "=?", new String[]{ String.valueOf(userId) });
    }

    public void delete(){
        SQLiteDatabase db = getWritableDatabase();
        db.delete(tableName, null, null);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    private void close_(){
        close();
    }
}
